package org.jks.excel;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.exception.ConstraintViolationException;
import org.jks.constant.Constants;
import org.jks.model.Device;
import org.jks.model.DeviceInOut;
import org.jks.model.DeviceStatus;
import org.jks.model.DeviceType;
import org.jks.model.Flight;
import org.jks.model.User;
import org.jks.response.model.ActionResult;
import org.jks.service.DeviceInOutService;
import org.jks.service.DeviceService;
import org.jks.service.DeviceStatusService;
import org.jks.service.DeviceTypeService;
import org.jks.service.FlightService;
import org.jks.util.DateUtil;
import org.jks.util.ExcelUtil;

public class DeviceUtil {
	public static List<String> columns = new ArrayList<String>();
	static {
		columns.add("设备编号");
		columns.add("二维码");
//		columns.add("设备名称");
		columns.add("设备类型");
		columns.add("设备品牌");
		columns.add("设备厂家");
		columns.add("设备型号");
		columns.add("投运时间");
		columns.add("设备状态");
		columns.add("是否备品");
		columns.add("所属班组"); ///
	}
	
	public static boolean checkColumnValid(String titles[]){
		int count = 0;
		for(String title: titles){
			if(columns.contains(title)){
				count++;
			}
		}
		return count == columns.size();
	}
	
	@SuppressWarnings("resource")
	public static void importExcel(HttpSession session, DeviceService deviceService, 
			DeviceTypeService deviceTypeService,
			DeviceStatusService deviceStatusService,
			DeviceInOutService deviceInOutService,
			FlightService flightService,
			InputStream is, ActionResult ar){
		XSSFWorkbook wb = null;
		XSSFSheet sheet = null;
		XSSFRow row = null;
		try {
			OPCPackage opcPackage = OPCPackage.open(is);
			wb = new XSSFWorkbook(opcPackage);
		}catch (Exception e) {
			ar.setSuccess(false);
			ar.setMsg(e.getMessage());
			e.printStackTrace();
			return;
		}
		sheet = wb.getSheetAt(0);
		row = sheet.getRow(0);
		int totalRows = sheet.getPhysicalNumberOfRows();
		int colNum = row.getPhysicalNumberOfCells();
		String[] titles = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			titles[i] = ExcelUtil.getCellFormatValue(row.getCell((short) i));
		}
		if(!checkColumnValid(titles)){
			ar.setSuccess(false);
			ar.setMsg("无效的excel");
			return;
		}
		Date inTime = new Date();
		User user = (User) session.getAttribute(Constants.SESSION_ACCOUNT);
		List<Device> devices = new ArrayList<Device>();
		List<DeviceInOut> deviceInOuts = new ArrayList<DeviceInOut>();
		for(int rownum=1; rownum<totalRows; rownum++){
			row = sheet.getRow(rownum);
			Device device = new Device();
			int index = 0;
			device.setNo(ExcelUtil.getCellFormatValue(row.getCell(index++)));
			String td = ExcelUtil.getCellFormatValue(row.getCell(index++));
			if(td.contains("."))
			{
				td = td.substring(0, td.indexOf("."));
			}
			
			device.setTdCode(td);
			
			String typeName = ExcelUtil.getCellFormatValue(row.getCell(index++));
			DeviceType deviceType = deviceTypeService.findByName(typeName);
			device.setType(deviceType);
			device.setBrand(ExcelUtil.getCellFormatValue(row.getCell(index++)));
			device.setManufacturers(ExcelUtil.getCellFormatValue(row.getCell(index++)));
			device.setModel(ExcelUtil.getCellFormatValue(row.getCell(index++)));
			String time = ExcelUtil.getCellFormatValue(row.getCell(index++));
			if(time.length() == 10){
				device.setOperationTime(DateUtil.toDate(time, "yyyy-MM-dd"));
			}
			else if(time.length() == 19){
				device.setOperationTime(DateUtil.toDate(time, "yyyy-MM-dd HH:mm:ss"));
			}
			String statusName = ExcelUtil.getCellFormatValue(row.getCell(index++));
			DeviceStatus deviceStatus = deviceStatusService.findByName(statusName);
			device.setCurrentStatus(deviceStatus);
			String isSparePart = ExcelUtil.getCellFormatValue(row.getCell(index++));
			if(isSparePart.trim().equals("是")){
				device.setIsSparePart(true);
			}
			String flightName = ExcelUtil.getCellFormatValue(row.getCell(index++));
			Flight flight = flightService.findByName(flightName);
			device.setFlight(flight);
			
			devices.add(device);
			
			DeviceInOut deviceInOut = new DeviceInOut();
			deviceInOut.setType(DeviceInOut.TYPE_IN);
			deviceInOut.setNo(device.getNo());
			deviceInOut.setTdCode(device.getTdCode());
			deviceInOut.setDeviceType(device.getType().getName());
			deviceInOut.setBrand(device.getBrand());
			deviceInOut.setManufacturers(device.getManufacturers());
			deviceInOut.setModel(device.getModel());
			deviceInOut.setOperator(user);
			deviceInOut.setTime(inTime);
			deviceInOuts.add(deviceInOut);
		}
		try{
			deviceService.save(devices);
			deviceInOutService.save(deviceInOuts);
			ar.setSuccess(true);
		}
		catch( ConstraintViolationException e){
			ar.setMsg("设备编号或二维码冲突");
		}
		catch(Exception e){
			ar.setMsg("添加设备失败");
		}
	}
}
